capture log close
clear all
set maxvar 50000
set more off
pause off
# delimit;

local crime "";
local output "";

/* 
Table B9 
Estimates adjusting for sample composition across periods
*/


clear;

foreach sample in prwora prworadenom { ;
	foreach method in rd iv {;
			use "`crime'/`sample'_bw264_crime_poly1allcov_`method'_all.dta" ;
			
			#delim;
			gen n_1crime2 = strpos(variables, "n_1crime2") ;
			gen n_2crime2 = strpos(variables, "n_2crime2") ;
			gen n_3crime2 = strpos(variables, "n_3crime2") ;
			gen n_4crime2 = strpos(variables, "n_4crime2") ;

			keep if n_1crime2 == 1 | n_2crime2 == 1 | n_3crime2 == 1 | n_4crime2 == 1 ;
			
			if "`method'" == "rd" {;

				keep variables n_post sen_post N ;
			
			};
			
			else {;
			
				keep if n_1crime2 == 1 | n_2crime2 == 1 | n_3crime2 == 1 | n_4crime2 == 1 ;
				
				keep variables n_age18unfav sen_age18unfav N;
			};

			replace variables = subinstr(variables,"_1997_2017", "", .) ;
			replace variables = subinstr(variables,"n_1crime2", "1_", .) ;
			replace variables = subinstr(variables,"n_2crime2", "2_", .) ;
			replace variables = subinstr(variables,"n_3crime2", "3_", .) ;
			replace variables = subinstr(variables,"n_4crime2", "4_", .) ;			
			replace variables = subinstr(variables,"_1997_2001", "", .) ;
			replace variables = subinstr(variables,"_2002_2006", "", .) ;
			replace variables = subinstr(variables,"_2007_2012", "", .) ;
			replace variables = subinstr(variables,"_2013_2017", "", .) ;

			#delim;

			if "`method'" == "rd" {;
			
			local crime "1_genr 2_genr 3_genr 4_genr 
			1_ahinc 2_ahinc 3_ahinc 4_ahinc" ;
			
			local crime_nm "1_genrnm 2_genrnm 3_genrnm 4_genrnm 
			1_ahincnm 2_ahincnm 3_ahincnm 4_ahincnm" ;

			};
			
			else {;
			
				local crime "1_genr 2_genr 3_genr 4_genr
				1_ahinc 2_ahinc 3_ahinc 4_ahinc" ;

				local crime_nm "1_genrnm 2_genrnm 3_genrnm 4_genrnm
				1_ahincnm 2_ahincnm 3_ahincnm 4_ahincnm" ;
			
			};
						 
			gen keep = 0 ;

			foreach var in `crime' {;

				replace keep = strpos(variables, "`var'") if keep == 0;

			};
			
			if "`sample'" == "prworadenom" {;
			
				foreach var in `crime_nm' {;

					replace keep = strpos(variables, "`var'") if keep == 0;

				};
			};

			drop if keep == 0 ;
			drop keep ;
			
			
			if "`sample'" == "prworadenom" {;

				gen flag = 1 ;
				replace flag = 2 if strpos(variables, "nm") ;
	
				foreach num in 1 2 3 4 {;
				
					replace variables = "`num'_genr" if  variables == "`num'_genrnm";
					replace variables = "`num'_genrt" if variables == "`num'_genrtnm";
					replace variables = "`num'_ahinc" if variables == "`num'_ahincnm"; 
				};	
			};
			
		replace variables = "Any income-generating charge (1997-01)" if variables == "1_genr";
		replace variables = "Number of income-generating charges (1997-01)" if variables == "1_genrt" ;
		replace variables = "Annual likelihood of incarceration (1997-01)" if variables == "1_ahinc" ;			
			
		replace variables = "Any income-generating charge (2002-06)" if variables == "2_genr";
		replace variables = "Number of income-generating charges (2002-06)" if variables == "2_genrt" ;			
		replace variables = "Annual likelihood of incarceration (2002-06)" if variables == "2_ahinc" ;			
			
		replace variables = "Any income-generating charge (2007-12)" if variables == "3_genr";
		replace variables = "Number of income-generating charges (2007-12)" if variables == "3_genrt" ;			
		replace variables = "Annual likelihood of incarceration (2007-12)" if variables == "3_ahinc" ;			
			
		replace variables = "Any income-generating charge (2013-17)" if variables == "4_genr";
		replace variables = "Number of income-generating charges (2013-17)" if variables == "4_genrt" ;			
		replace variables = "Annual likelihood of incarceration (2013-17)" if variables == "4_ahinc" ;		
					
		sort variables ;

			if "`method'" == "rd" {;

				rename n_post pt_est_rd ;
				rename sen_post se_rd ;
				gen star_rd = 2 * (normal(-abs(pt_est_rd/se_rd))) ;
				gen sta_sign_rd = "" ;
				replace sta_sign_rd = "*" if star_rd <= 0.1 & star_rd > 0.05 ;
				replace sta_sign_rd = "**" if star_rd <= 0.05 & star_rd > 0.01 ;
				replace sta_sign_rd = "***" if star_rd <= 0.01 ;
				drop star_rd ;
			};
			
			else {;
				
				rename n_age18unfav pt_est_iv ;
				rename sen_age18unfav se_iv ;
				gen star_iv = 2 * (normal(-abs(pt_est_iv/se_iv))) ;
				gen sta_sign_iv = "" ;
				replace sta_sign_iv = "*" if star_iv <= 0.1 & star_iv > 0.05 ;
				replace sta_sign_iv = "**" if star_iv <= 0.05 & star_iv > 0.01 ;
				replace sta_sign_iv = "***" if star_iv <= 0.01 ;
				drop star_iv ;
			};

			
		order variables pt_est* se* sta_sign* N;
			
			if "`sample'" == "prworadenom" {;
			
				tostring flag, replace ;
			
				replace flag = "_denom" if flag == "1" ;
				replace flag = "_nm" if flag == "2" ;
						
				reshape wide pt_est* se* sta_sign* N, i(variables) j(flag) string ;
	 
				tostring se*, replace ;
				replace se_`method'_nm = "(" + se_`method'_nm + ")" ;
				replace se_`method'_denom = "(" + se_`method'_denom + ")" ;
			
			} ;
			
			else {;
				
				rename (pt_est_`method' se_`method' sta_sign_`method' N) (pt_est_`method'_bl se_`method'_bl sta_sign_`method'_bl N_bl);
				
				tostring se*, replace ;
				replace se_`method' = "(" + se_`method' + ")" ;
			
			};
			
	tempfile `sample'_`method'_all ;
	save ``sample'_`method'_all' ;		
			
	} ; 
} ;		


foreach sample in prwora prworadenom { ;
	
	use ``sample'_rd_all', clear ;
		
	merge 1:1 variables using ``sample'_iv_all' ;
	drop _merge ;
		
		if "`sample'" == "prworadenom" { ;
	
			order variables pt_est_rd_denom sta_sign_rd_denom se_rd_denom pt_est_iv_denom sta_sign_iv_denom se_iv_denom N_denom pt_est_rd_nm sta_sign_rd_nm se_rd_nm pt_est_iv_nm sta_sign_iv_nm se_iv_nm N_nm ;
		
		} ;
		
		else { ;
	
		order variables pt_est_rd_bl sta_sign_rd_bl se_rd_bl pt_est_iv_bl sta_sign_iv_bl se_iv_bl N_bl ;
		
		} ;
	
	gen order = 1 if variables == "Any income-generating charge (1997-01)" ;
	replace order = 2 if variables == "Number of income-generating charges (1997-01)" ;
	replace order = 3 if variables == "Annual likelihood of incarceration (1997-01)" ;	
	replace order = 4 if variables == "Any income-generating charge (2002-06)" ;
	replace order = 5 if variables == "Number of income-generating charges (2002-06)" ;	
	replace order = 6 if variables == "Annual likelihood of incarceration (2002-06)" ;
	replace order = 7 if variables == "Any income-generating charge (2007-12)" ;
	replace order = 8 if variables == "Number of income-generating charges (2007-12)" ;	
	replace order = 9 if variables == "Annual likelihood of incarceration (2007-12)" ;
	replace order = 10 if variables == "Any income-generating charge (2013-17)" ;
	replace order = 11 if variables == "Number of income-generating charges (2013-17)" ;
	replace order = 12 if variables == "Annual likelihood of incarceration (2013-17)" ;
	
	
	tempfile `sample'_compiled ;
	save ``sample'_compiled' ;
	
} ;	


use `prwora_compiled' , clear ;
	
	merge 1:1 variables using `prworadenom_compiled' ;
	
	sort order ;
		
	drop order ;

	
export excel using "`output'/tabB9.xls", firstrow(variables) ;

